package com.white.note.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Build;

import androidx.annotation.Nullable;

import com.white.note.entity.NoteInfo;

import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;

public class NoteDbHelper extends SQLiteOpenHelper {
    private static NoteDbHelper sHelper;
    private static final String DB_NAME = "note.db";
    private static final int VERSION = 1;

    public NoteDbHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    // 创建单例，供使用条用改类里面的增删改查的方法
    public synchronized static NoteDbHelper getInstance(Context context) {
        if (null == sHelper) {
            sHelper = new NoteDbHelper(context, DB_NAME, null, VERSION);
        }
        return sHelper;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 建表
        db.execSQL("create table note_table(note_id integer primary key autoincrement, " + "username text," + // 用户名
                "note_title text," + // 标题
                "note_content text," + // 内容
                "note_create_time text," + // 创建时间
                "note_is_top integer" + // 是否置顶
                ")");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    /**
     * 创建备忘录
     */
    public int createNote(String username, String note_title, String note_content) {
        // 获取SQLiteDatabase实例
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        // 填充占位符
        values.put("username", username);
        values.put("note_title", note_title);
        values.put("note_content", note_content);
        values.put("note_create_time", getCurrentTime());
        values.put("note_is_top", 0);
        String nullColumnHack = "values(null,?,?,?,?,?)";
        // 执行
        int insert = (int) db.insert("note_table", nullColumnHack, values);
        // db.close();
        return insert;
    }

    /**
     * 查询用户的备忘录
     */
    @SuppressLint("Range")
    public List<NoteInfo> queryNoteListDate(String username) {
        SQLiteDatabase db = getReadableDatabase();
        List<NoteInfo> noteInfoList = new ArrayList<>();
        String sql = "select note_id,username,note_title,note_content,note_create_time,note_is_top from note_table where username=? order by note_is_top desc";
        String[] selectionArgs = {username};
        Cursor cursor = db.rawQuery(sql, selectionArgs);
        while (cursor.moveToNext()) {
            int note_id = cursor.getInt(cursor.getColumnIndex("note_id"));
            String userName = cursor.getString(cursor.getColumnIndex("username"));
            String note_title = cursor.getString(cursor.getColumnIndex("note_title"));
            String note_content = cursor.getString(cursor.getColumnIndex("note_content"));
            String note_create_time = cursor.getString(cursor.getColumnIndex("note_create_time"));
            int note_is_top = cursor.getInt(cursor.getColumnIndex("note_is_top"));
            noteInfoList.add(new NoteInfo(note_id, userName, note_title, note_content, note_create_time, note_is_top));
        }
        cursor.close();
        db.close();
        return noteInfoList;
    }

    /**
     * 查询用户的指定备忘录
     */
    @SuppressLint("Range")
    public List<NoteInfo> searchUserNote(String username, String content) {
        SQLiteDatabase db = getReadableDatabase();
        List<NoteInfo> noteInfoList = new ArrayList<>();
        String sql = "select note_id,username,note_title,note_content,note_create_time,note_is_top from note_table where username=? and note_title like ?";
        String[] selectionArgs = {username, "%" + content + "%"};
        Cursor cursor = db.rawQuery(sql, selectionArgs);
        while (cursor.moveToNext()) {
            int note_id = cursor.getInt(cursor.getColumnIndex("note_id"));
            String userName = cursor.getString(cursor.getColumnIndex("username"));
            String note_title = cursor.getString(cursor.getColumnIndex("note_title"));
            String note_content = cursor.getString(cursor.getColumnIndex("note_content"));
            String note_create_time = cursor.getString(cursor.getColumnIndex("note_create_time"));
            int note_is_top = cursor.getInt(cursor.getColumnIndex("note_is_top"));
            noteInfoList.add(new NoteInfo(note_id, userName, note_title, note_content, note_create_time, note_is_top));
        }
        cursor.close();
        db.close();
        return noteInfoList;
    }

    /**
     * 删除备忘录
     *
     * @param id
     * @return
     */
    public int deleteNote(int id) {
        SQLiteDatabase db = getWritableDatabase();
        int deleteCount = db.delete("note_table", "note_id=?", new String[]{String.valueOf(id)});
        db.close();
        return deleteCount;
    }

    /**
     * 更新置顶
     *
     * @return
     */
    public int updateNoteTop(int id, int top) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("note_is_top", top);
        int row = db.update("note_table", values, "note_id=?", new String[]{String.valueOf(id)});
        return row;
    }

    /**
     * 编辑备忘录
     *
     * @param noteId
     * @param title
     * @param content
     * @return
     */
    public int editNote(int noteId, String title, String content) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("note_title", title);
        values.put("note_content", content);
        int row = db.update("note_table", values, "note_id=?", new String[]{String.valueOf(noteId)});
        return row;
    }

    /**
     * 清空表
     *
     * @return
     */
    public void clearNote(String userName) {
        SQLiteDatabase db = getWritableDatabase();
        db.delete("note_table", "username=?", new String[]{String.valueOf(userName)});
    }

    private String getCurrentTime() {
        String formattedDate;
        if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.O) {
            // API 26及以上：使用LocalDate（推荐）
            LocalDate currentDate = LocalDate.now();
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            formattedDate = currentDate.format(formatter);
        } else {
            // API 26以下：使用Calendar + SimpleDateFormat（兼容方案）
            Calendar calendar = Calendar.getInstance();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd", Locale.getDefault());
            formattedDate = sdf.format(calendar.getTime());
        }
        // 后续使用formattedDate（此时已确保非null）
        System.out.println(formattedDate); // 输出如：2025-10-11
        return formattedDate;
    }

}
